package com.yxt.service;

import java.util.ArrayList;
import java.util.List;

import com.yxt.common.sys.StringUtil;
import com.yxt.dao.DBOperator;
import com.yxt.model.Address;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class AddressService {
	private DBOperator dbHelper;

	public AddressService(Context context) {
		dbHelper = new DBOperator(context);
	}

	public void saveAddress(Address address) {
		SQLiteDatabase db = dbHelper.getDataBase();
		db.execSQL("insert into PROVINCE_PERSON (id,departmentid,burdentype,name,job,sfjob,officetel,hometel,mobiltel,personorder,personyear,officetel2,officefax,uptime,stb_pcode,stb_ccode,stb_tcode,pinyin,orderid) values (? , ? , ? , ?, ?,? , ? , ? , ?, ?,? , ? , ? , ?, ?,? , ? , ? , ?)",
		new Object[] {address.getId(),address.getDepartmentid(),address.getBurdentype(),address.getName(),address.getJob(),address.getSfjob(),address.getOfficetel(),address.getHometel(),address.getMobiltel(),address.getPersonorder(),address.getPersonyear(),address.getOfficetel2(),address.getOfficefax(),address.getUptime(),address.getStb_pcode(),address.getStb_ccode(),address.getStb_tcode(),address.getPinyin(),address.getOrderid() });
	}
	
	public Address getAddressById(String id) {
		Address addr = new Address();
		SQLiteDatabase db = dbHelper.getDataBase();
		Cursor cs = db.rawQuery("select id,departmentid,burdentype,name,job,sfjob,officetel,hometel,mobiltel,personorder,personyear,officetel2,officefax,uptime,stb_pcode,stb_ccode,stb_tcode,pinyin,orderid from PROVINCE_PERSON where id = ? ",
				new String[] { String.valueOf(id) });
		
		if (cs.moveToNext()) {
			int index = 0;
			addr.setId(cs.getString(index++));
			addr.setDepartmentid(cs.getString(index++));
			addr.setBurdentype(cs.getString(index++));
			addr.setName(cs.getString(index++));
			addr.setJob(cs.getString(index++));
			addr.setSfjob(cs.getString(index++));
			addr.setOfficetel(cs.getString(index++));
			addr.setHometel(cs.getString(index++));
			addr.setMobiltel(cs.getString(index++));
			addr.setPersonorder(cs.getString(index++));
			addr.setPersonyear(cs.getString(index++));
			addr.setOfficetel2(cs.getString(index++));
			addr.setOfficefax(cs.getString(index++));
			addr.setUptime(cs.getString(index++));
			addr.setStb_pcode(cs.getString(index++));
			addr.setStb_ccode(cs.getString(index++));
			addr.setStb_tcode(cs.getString(index++));
			addr.setPinyin(cs.getString(index++));
			addr.setOrderid(cs.getString(index++));
		}
		cs.close();
		return addr;
	}
	
	public List<Address> getAddressList(String pcode, String dcode) {
		List<Address> list = new ArrayList<Address>();
		SQLiteDatabase db = dbHelper.getDataBase();
		StringBuffer sql = new StringBuffer("select id,departmentid,burdentype,name,job,sfjob,officetel,hometel,mobiltel,personorder,personyear,officetel2,officefax,uptime,stb_pcode,stb_ccode,stb_tcode,pinyin,orderid from PROVINCE_PERSON ");
		boolean isNotEmpty = false;
		if (StringUtil.isNotEmpty(pcode)) {
			sql.append(" WHERE stb_pcode = '" +pcode+ "' ");
			isNotEmpty = true;
		}
		if (StringUtil.isNotEmpty(dcode)) {
			if (isNotEmpty) {
				sql.append(" AND ");
			} else {
				sql.append(" WHERE ");
			}
			sql.append(" departmentid = '" +dcode+ "' ");
		}
		sql.append(" ORDER BY stb_pcode");
		Cursor cs = db.rawQuery(sql.toString(),null);		
		while (cs.moveToNext()) {
			int index = 0;
			Address address = new Address();
			address.setId(cs.getString(index++));
			address.setDepartmentid(cs.getString(index++));
			address.setBurdentype(cs.getString(index++));
			address.setName(cs.getString(index++));
			address.setJob(cs.getString(index++));
			address.setSfjob(cs.getString(index++));
			address.setOfficetel(cs.getString(index++));
			address.setHometel(cs.getString(index++));
			address.setMobiltel(cs.getString(index++));
			address.setPersonorder(cs.getString(index++));
			address.setPersonyear(cs.getString(index++));
			address.setOfficetel2(cs.getString(index++));
			address.setOfficefax(cs.getString(index++));
			address.setUptime(cs.getString(index++));
			address.setStb_pcode(cs.getString(index++));
			address.setStb_ccode(cs.getString(index++));
			address.setStb_tcode(cs.getString(index++));
			address.setPinyin(cs.getString(index++));
			address.setOrderid(cs.getString(index++));
			list.add(address);
		}
		cs.close();
		return list;
	}
	
	public List<Address> getAddressListByCondition(String column,String value) {
		List<Address> list = new ArrayList<Address>();
		SQLiteDatabase db = dbHelper.getDataBase();
		StringBuffer sql = new StringBuffer();
		sql.append("select id,departmentid,burdentype,name,job,sfjob,officetel,hometel,mobiltel,personorder,personyear,officetel2,officefax,uptime,stb_pcode,stb_ccode,stb_tcode,pinyin,orderid from PROVINCE_PERSON");
		sql.append(" where ");
		sql.append(column);
		sql.append(" like '%");
		sql.append(value);
		sql.append("%'");
		sql.append(" ORDER BY stb_pcode");
		Cursor cs = db.rawQuery(sql.toString(),null);		
		while (cs.moveToNext()) {
			int index = 0;
			Address address = new Address();
			address.setId(cs.getString(index++));
			address.setDepartmentid(cs.getString(index++));
			address.setBurdentype(cs.getString(index++));
			address.setName(cs.getString(index++));
			address.setJob(cs.getString(index++));
			address.setSfjob(cs.getString(index++));
			address.setOfficetel(cs.getString(index++));
			address.setHometel(cs.getString(index++));
			address.setMobiltel(cs.getString(index++));
			address.setPersonorder(cs.getString(index++));
			address.setPersonyear(cs.getString(index++));
			address.setOfficetel2(cs.getString(index++));
			address.setOfficefax(cs.getString(index++));
			address.setUptime(cs.getString(index++));
			address.setStb_pcode(cs.getString(index++));
			address.setStb_ccode(cs.getString(index++));
			address.setStb_tcode(cs.getString(index++));
			address.setPinyin(cs.getString(index++));
			address.setOrderid(cs.getString(index++));
			list.add(address);
		}
		cs.close();
		return list;
	}
	
	public int getAddressCount(){
		int result = 0;
		SQLiteDatabase db = dbHelper.getDataBase();
		Cursor cs = db.rawQuery("select count(*) from PROVINCE_PERSON",null);
		if(cs.moveToNext()){
			result = cs.getInt(0);
		}
		return result;
	}
	
	public List<String> getEnumProvinces() {
		List<String> list = new ArrayList<String>();
		SQLiteDatabase db = dbHelper.getDataBase();
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct stb_pcode  from PROVINCE_PERSON ");
		Cursor cs = db.rawQuery(sql.toString(),null);

		while (cs.moveToNext()) {
			list.add(cs.getString(0));			
		}
		cs.close();
		return list;
	}
	
	public List<String> getEnumDepartmentByProvince(String provinces) {
		List<String> list = new ArrayList<String>();
		SQLiteDatabase db = dbHelper.getDataBase();
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct departmentid  from PROVINCE_PERSON where stb_pcode='"+provinces+"'");
		Cursor cs = db.rawQuery(sql.toString(),null);
		while (cs.moveToNext()) {
			list.add(cs.getString(0));			
		}
		cs.close();
		return list;
	}
	
	public void deleteAddress() {
		SQLiteDatabase db = dbHelper.getDataBase();
		db.execSQL("delete from PROVINCE_PERSON");
	}

	public void closeDB() {
		dbHelper.close();
	}

}
